﻿CREATE TABLE [dbo].[EntityRelationships] (
    [Entity1ID]             INT            NOT NULL,
    [Entity2ID]             INT            NOT NULL,
    [ShortRelationshipName] NVARCHAR (12)  NOT NULL,
    [StartDate]             DATETIME       NULL,
    [EndDate]               DATETIME       NULL,
    [Notes]                 NVARCHAR (255) NULL,
    CONSTRAINT [FK_EntityRelationships_DefinedEntityRelationships] FOREIGN KEY ([ShortRelationshipName]) REFERENCES [dbo].[DefinedEntityRelationships] ([ShortRelationshipName]),
    CONSTRAINT [FK_EntityRelationships_Entity] FOREIGN KEY ([Entity1ID]) REFERENCES [dbo].[Entities] ([EntityID]),
    CONSTRAINT [FK_EntityRelationships_Entity1] FOREIGN KEY ([Entity2ID]) REFERENCES [dbo].[Entities] ([EntityID])
);





